package org.sixtynine.stock.service.file;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.Restrictions;
import org.sixtynine.stock.Constants;
import org.sixtynine.stock.ReturnData;
import org.sixtynine.stock.entity.BaseEntity;
import org.sixtynine.stock.entity.Company;
import org.sixtynine.stock.entity.QuarterlyShareData;
import org.sixtynine.stock.service.GenericService;
import org.sixtynine.stock.service.business.StockUtil;
import org.sixtynine.stock.util.DateUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

@Component(ExcelProcessorFactory.FILE_TYPE_QUARTER_SHARE_DATA_LIST)
public class QuarterDataProcesser extends CompanyDataListProcessor {

	Date formatedDate;

	@Autowired
	GenericService genericService;

	private static Map<String, QuarterlyShareData> exsistingDataMap;

	public static String getDataString(Date date) {
		Calendar calendar = Calendar.getInstance();
		calendar.setTime(date);

		return (calendar.get(Calendar.YEAR) + "_"
				+ calendar.get(Calendar.MONTH) + "_" + calendar
					.get(Calendar.DATE));
	}

	private static void processExsistingRecords(List<BaseEntity> dataList) {
		exsistingDataMap = new HashMap<String, QuarterlyShareData>();

		for (BaseEntity baseEntity : dataList) {
			QuarterlyShareData quarterlyShareData = (QuarterlyShareData) baseEntity;
			exsistingDataMap.put(quarterlyShareData._getFullQuarterCode(),
					quarterlyShareData);

		}
	}

	@Override
	public ReturnData<List<? extends BaseEntity>> process(String filePath) {
		ReturnData<List<? extends BaseEntity>> returnData = new ReturnData<List<? extends BaseEntity>>(
				Constants.SUCCESS, "");

		// initialized if maps are not created
		if (companyMap == null) {
			init();
		}

		try {

			List<QuarterlyShareData> quarterlyShareDataList = new ArrayList<QuarterlyShareData>();
			File file = new File(filePath);

			// Get the workbook instance for XLS file
			Workbook workbook = WorkbookFactory.create(file);

			// Get first sheet from the workbook
			Sheet sheet = workbook.getSheetAt(0);

			// Get iterator to all the rows in current sheet
			Iterator<Row> rowIterator = sheet.iterator();
			int rowIndex = 0;

			String fileName = file.getName();
			String quarterCode = getFormattedQuarter(fileName);

			int yearCode = getFormattedYear(fileName);

			// check file name
			if (!StockUtil.getFileName(fileName).equals(Constants.QUARTER_DATA)) {
				returnData.setNo(Constants.ERROR);
				returnData.setMsg("Please add quarter data only");
				return returnData;
			}

			if (checkQtrCode(quarterCode) > 4) {
				returnData.setNo(Constants.ERROR);
				returnData.setMsg("Quarter code " + quarterCode + " is wrong");

			}
			if (checkQtrCode(quarterCode) < 1) {
				returnData.setNo(Constants.ERROR);
				returnData.setMsg("Quarter code " + quarterCode + " is wrong");
			}
			if (checkFutureQtr(quarterCode)) {
				returnData.setNo(Constants.ERROR);
				returnData.setMsg("Please enter correct year");
			}

			Criterion criterion = (Restrictions.conjunction().add(
					Restrictions.eq("year", yearCode)).add(Restrictions.eq(
					"quarterCode", quarterCode)));
			List<BaseEntity> dataList = genericService.findByCriteria(
					criterion, QuarterlyShareData.class);
			if (dataList.size() > 0) {
				processExsistingRecords(dataList);
			}

			String companyCode = null;
			// It is better to define variables outside loops
			double averageEquity, averageTotalAsse, cash, debt, ebitda, instOwnership, percentOwnbyInsiders, profit, profitAfterTax, revenues, sharesInFreefloat, weightedAverage, totShareOutstand, lastFourQtrweightedAva, equityToSharehol = 0.00;

			while (rowIterator.hasNext()) {
				QuarterlyShareData quarterlyShareData = null;
				companyCode = null;
				averageEquity = 0.00;
				averageTotalAsse = 0.00;
				cash = 0.00;
				debt = 0.00;
				ebitda = 0.00;
				instOwnership = 0.00;
				percentOwnbyInsiders = 0.00;
				profit = 0.00;
				profitAfterTax = 0.00;
				revenues = 0.00;
				sharesInFreefloat = 0.00;
				weightedAverage = 0.00;

				totShareOutstand = 0.00;
				equityToSharehol = 0.00;

				lastFourQtrweightedAva = 0.00;

				Row row = rowIterator.next();
				// Get iterator to all cells of current row
				Iterator<Cell> cellIterator = row.cellIterator();

				if (rowIndex == 0) {
					Cell firstCell = row.getCell(0);
					if (firstCell == null) {
						returnData.setNo(Constants.ERROR);
						returnData
								.setMsg("Error file format, data should be started from first row and column");
						return returnData;
					}
					rowIndex++;
					continue;
				}

				int colIndex = 0;
				while (cellIterator.hasNext()) {
					Cell cell = cellIterator.next();
					Object cellVal = getCellValue(cell);
					switch (colIndex) {
					case 0:
						ReturnData<?> codeReturn = validateCompanyCode(cellVal,
								rowIndex);
						if (codeReturn._isSuccess()) {
							companyCode = (String) cellVal;
						} else {
							returnData.setNo(Constants.ERROR);
							returnData.setMsg(codeReturn.getMsg());
							return returnData;
						}
						break;
					case 1:
						weightedAverage = (Double) cellVal;
						break;

					case 2:
						totShareOutstand = (Double) cellVal;
						break;

					case 3:
						sharesInFreefloat = (Double) cellVal;
						break;

					case 4:
						cash = (Double) cellVal;
						break;

					case 5:
						equityToSharehol = (Double) cellVal;
						break;

					case 6:
						averageEquity = (Double) cellVal;
						break;

					case 7:
						averageTotalAsse = (Double) cellVal;
						break;

					case 8:
						debt = (Double) cellVal;
						break;

					case 9:
						profitAfterTax = (Double) cellVal;
						break;

					case 10:
						profit = (Double) cellVal;
						break;

					case 11:
						ebitda = (Double) cellVal;
						break;

					case 12:
						revenues = (Double) cellVal;
						break;

					case 13:
						percentOwnbyInsiders = (Double) cellVal;
						break;

					case 14:
						instOwnership = (Double) cellVal;
						break;

					case 15:
						lastFourQtrweightedAva = (Double) cellVal;
						break;

					default:
						break;
					}

					colIndex++;
				}

				Company company = companyMap.get(companyCode);

				if (company == null) {
					returnData.setNo(Constants.ERROR);
					returnData
							.setMsg("Company Code cannot find for company code "
									+ companyCode);

					return returnData;
				}

				if (dataList.size() > 0) {
					String fullQtrCode = quarterCode + "_" + yearCode;

					if (exsistingDataMap.containsKey(fullQtrCode)) {

						if (exsistingDataMap.get(fullQtrCode).getCompany()
								.getId() == company.getId()) {
							quarterlyShareData = exsistingDataMap
									.get(fullQtrCode);
						} else {
							quarterlyShareData = new QuarterlyShareData();
						}
					} else {
						quarterlyShareData = new QuarterlyShareData();
					}
				} else {
					quarterlyShareData = new QuarterlyShareData();
				}

				quarterlyShareData.setCompany(company);
				quarterlyShareData.setAverageEquity(averageEquity);
				quarterlyShareData.setAverageTotalAsse(averageTotalAsse);
				quarterlyShareData.setCash(cash);
				quarterlyShareData.setDebt(debt);
				quarterlyShareData.setEbitda(ebitda);
				quarterlyShareData.setInstOwnership(instOwnership);
				quarterlyShareData
						.setPercentOwnbyInsiders(percentOwnbyInsiders);
				quarterlyShareData.setProfit(profit);
				quarterlyShareData.setProfitAfterTax(profitAfterTax);
				quarterlyShareData.setRevenues(revenues);
				quarterlyShareData.setSharesInFreefloat(sharesInFreefloat);
				quarterlyShareData.setWeightedAverage(weightedAverage);
				quarterlyShareData.setQuarterCode(quarterCode);
				quarterlyShareData.setYear(yearCode);
				quarterlyShareData.setTotShareOutstand(totShareOutstand);
				quarterlyShareData.setEquityToSharehol(equityToSharehol);
				quarterlyShareData
						.setLstFourQtrWeighted(lastFourQtrweightedAva);

				quarterlyShareDataList.add(quarterlyShareData);

				rowIndex++;

			}

			returnData.setData(quarterlyShareDataList);

		} catch (IOException e) {
			e.printStackTrace();
			returnData.setNo(Constants.ERROR);
			returnData.setMsg(e.getMessage());
		} catch (InvalidFormatException e) {
			e.printStackTrace();
			returnData.setNo(Constants.ERROR);
			returnData.setMsg(e.getMessage());
		}

		return returnData;
	}

	public String getFormattedQuarter(String fileName) {
		String quarter = null;
		String dteArry[] = fileName.split("_", 3);
		String dateS = dteArry[0];

		quarter = dateS;

		return quarter;
	}

	public int getFormattedYear(String fileName) {
		int quarter = 0;
		String dteArry[] = fileName.split("_", 3);
		quarter = Integer.parseInt(dteArry[1]);

		return quarter;
	}

	private ReturnData<?> validateCompanyCode(Object cellVal, int row) {
		ReturnData<String> returnData = new ReturnData<String>(
				Constants.SUCCESS, "");

		if (!validateForNull(cellVal)) {
			returnData.setNo(Constants.ERROR);
			returnData.setMsg("Company code cannot be null at row " + row);
		}

		if (cellVal instanceof String) {
			String companyCode = (String) cellVal;

			// TODO validate format - with regular expression ?

		} else {
			returnData.setNo(Constants.ERROR);
			returnData.setMsg("Company code is in the wrong format at row "
					+ row);
		}

		return returnData;
	}

	public int checkQtrCode(String code) {
		String qtrCode = code.split("_")[1];
		String qtr = qtrCode.substring(1);

		return Integer.parseInt(qtr);
	}

	public boolean checkFutureQtr(String code) {

		int year = Integer.parseInt(code.split("_")[0]);
		int currentYear = DateUtil.getYearFromDate(new Date());

		return currentYear < year;
	}

}
